package net.hn.hnms.biz.indicator.dao;

import net.hn.hnms.biz.indicator.entity.SysIndicatorModel;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author 智呈物联 nzw
 * @date 2022/8/24
 */
@Repository
public interface SysIndicatorModelPercentMapper extends JpaRepository<SysIndicatorModel,String> {

    @Query(value = "SELECT\n" +
            "\t'瓦斯' indicatorFirstLevelName,\n" +
            "\t'1' AS indicatorSecondLevelName,\n" +
            "\tSUM ( sim.indicator_value ) totalSum \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model sim,\n" +
            "\t( SELECT MAX ( data_time ) maxTime FROM ssys_mine_indicator_model WHERE to_char( data_time, 'yyyy-MM-dd' ) = ?1 AND indicator_first_level_code = ?2 ) maxTime \n" +
            "WHERE\n" +
            "\tsim.data_time = maxTime.maxTime \n" +
            "\tAND indicator_first_level_code = ?2 \n" +
            "\tAND sim.mine_code = ?7 \n"+
            "\tAND (indicator_third_level_code = '' or indicator_third_level_code is null) UNION ALL\n" +
            "SELECT\n" +
            "\t'火灾' indicatorFirstLevelName,\n" +
            "\t'1' AS indicatorSecondLevelName,\n" +
            "\tSUM ( sim.indicator_value ) totalSum \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model sim,\n" +
            "\t( SELECT MAX ( data_time ) maxTime FROM ssys_mine_indicator_model WHERE to_char( data_time, 'yyyy-MM-dd' ) = ?1 AND indicator_first_level_code = ?3 ) maxTime \n" +
            "WHERE\n" +
            "\tsim.data_time = maxTime.maxTime \n" +
            "\tAND indicator_first_level_code = ?3 \n" +
            "\tAND sim.mine_code = ?7 \n"+
            "\tAND (indicator_third_level_code = '' or indicator_third_level_code is null) UNION ALL\n" +
            "SELECT\n" +
            "\t'粉尘' indicatorFirstLevelName,\n" +
            "\t'1' AS indicatorSecondLevelName,\n" +
            "\tSUM ( sim.indicator_value ) totalSum \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model sim,\n" +
            "\t( SELECT MAX ( data_time ) maxTime FROM ssys_mine_indicator_model WHERE to_char( data_time, 'yyyy-MM-dd' ) = ?1 AND indicator_first_level_code = ?4 ) maxTime \n" +
            "WHERE\n" +
            "\tsim.data_time = maxTime.maxTime \n" +
            "\tAND indicator_first_level_code = ?4 \n" +
            "\tAND sim.mine_code = ?7 \n"+
            "\tAND (indicator_third_level_code = '' or indicator_third_level_code is null) UNION ALL\n" +
            "SELECT\n" +
            "\t'水害' indicatorFirstLevelName,\n" +
            "\t'1' AS indicatorSecondLevelName,\n" +
            "\tSUM ( sim.indicator_value ) totalSum \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model sim,\n" +
            "\t( SELECT MAX ( data_time ) maxTime FROM ssys_mine_indicator_model WHERE to_char( data_time, 'yyyy-MM-dd' ) = ?1 AND indicator_first_level_code = ?5  ) maxTime \n" +
            "WHERE\n" +
            "\tsim.data_time = maxTime.maxTime \n" +
            "\tAND indicator_first_level_code = ?5 \n" +
            "\tAND sim.mine_code = ?7 \n"+
            "\tAND (indicator_third_level_code = '' or indicator_third_level_code is null) UNION ALL\n" +
            "SELECT\n" +
            "\t'顶板' indicatorFirstLevelName,\n" +
            "\t'1' AS indicatorSecondLevelName,\n" +
            "\tSUM ( sim.indicator_value ) totalSum \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model sim,\n" +
            "\t( SELECT MAX ( data_time ) maxTime FROM ssys_mine_indicator_model WHERE to_char( data_time, 'yyyy-MM-dd' ) = ?1 AND indicator_first_level_code = ?6  ) maxTime \n" +
            "WHERE\n" +
            "\tsim.data_time = maxTime.maxTime \n" +
            "\tAND sim.indicator_first_level_code = ?6 \n" +
            "\tAND (sim.indicator_third_level_code = '' or sim.indicator_third_level_code is null) " +
            "\tAND sim.mine_code = ?7",nativeQuery = true)
    List<Map<String,String>> queryModelListV2(
            @Param("dateParam") String dateParam
            ,@Param("gasCode")String gasCode
            ,@Param("fireCode")String fireCode
            ,@Param("dustCode")String dustCode
            ,@Param("waterCode")String waterCode
            ,@Param("roofCode") String roofCode
            ,@Param("mineCode") String mineCode);

    @Query(value = "SELECT\n" +
            "\t'瓦斯' indicatorFirstLevelName,\n" +
            "\t'1' AS indicatorSecondLevelName,\n" +
            "\tSUM ( sim.indicator_value ) totalSum, sim.mine_code \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model sim,\n" +
            "\t( SELECT MAX ( data_time ) maxTime, mine_code FROM ssys_mine_indicator_model " +
            "WHERE " +
//            "to_char( data_time, 'yyyy-MM-dd' ) = ?1 AND" +
            " indicator_first_level_code = ?1 " +
            " group by mine_code) maxTime \n" +
            "WHERE\n" +
            "\tsim.data_time = maxTime.maxTime \n" +
            "and sim.mine_code = maxTime.mine_code " +
            "\tAND indicator_first_level_code = ?1 \n" +
            "\tAND (indicator_third_level_code = '' or indicator_third_level_code is null) group by sim.mine_code UNION ALL\n" +
            "SELECT\n" +
            "\t'火灾' indicatorFirstLevelName,\n" +
            "\t'1' AS indicatorSecondLevelName,\n" +
            "\tSUM ( sim.indicator_value ) totalSum, sim.mine_code \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model sim,\n" +
            "\t( SELECT MAX ( data_time ) maxTime, mine_code FROM ssys_mine_indicator_model " +
            "WHERE " +
//            "to_char( data_time, 'yyyy-MM-dd' ) = ?1 AND " +
            "indicator_first_level_code = ?2 group by mine_code ) maxTime \n" +
            "WHERE\n" +
            "\tsim.data_time = maxTime.maxTime \n" +
            "and sim.mine_code = maxTime.mine_code " +
            "\tAND indicator_first_level_code = ?2 \n" +
            "\tAND (indicator_third_level_code = '' or indicator_third_level_code is null) group by sim.mine_code UNION ALL\n" +
            "SELECT\n" +
            "\t'粉尘' indicatorFirstLevelName,\n" +
            "\t'1' AS indicatorSecondLevelName,\n" +
            "\tSUM ( sim.indicator_value ) totalSum, sim.mine_code \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model sim,\n" +
            "\t( SELECT MAX ( data_time ) maxTime, mine_code FROM ssys_mine_indicator_model " +
            "WHERE " +
//            "to_char( data_time, 'yyyy-MM-dd' ) = ?1 AND" +
            " indicator_first_level_code = ?3  group by mine_code ) maxTime \n" +
            "WHERE\n" +
            "\tsim.data_time = maxTime.maxTime \n" +
            "and sim.mine_code = maxTime.mine_code " +
            "\tAND indicator_first_level_code = ?3 \n" +
            "\tAND (indicator_third_level_code = '' or indicator_third_level_code is null) group by sim.mine_code UNION ALL\n" +
            "SELECT\n" +
            "\t'水害' indicatorFirstLevelName,\n" +
            "\t'1' AS indicatorSecondLevelName,\n" +
            "\tSUM ( sim.indicator_value ) totalSum, sim.mine_code \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model sim,\n" +
            "\t( SELECT MAX ( data_time ) maxTime, mine_code FROM ssys_mine_indicator_model " +
            "WHERE " +
//            "to_char( data_time, 'yyyy-MM-dd' ) = ?1 AND" +
            " indicator_first_level_code = ?4  group by mine_code ) maxTime \n" +
            "WHERE\n" +
            "\tsim.data_time = maxTime.maxTime \n" +
            "and sim.mine_code = maxTime.mine_code " +
            "\tAND indicator_first_level_code = ?4 \n" +
            "\tAND (indicator_third_level_code = '' or indicator_third_level_code is null) group by sim.mine_code UNION ALL\n" +
            "SELECT\n" +
            "\t'顶板' indicatorFirstLevelName,\n" +
            "\t'1' AS indicatorSecondLevelName,\n" +
            "\tSUM ( sim.indicator_value ) totalSum, sim.mine_code \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model sim,\n" +
            "\t( SELECT MAX ( data_time ) maxTime, mine_code FROM ssys_mine_indicator_model " +
            "WHERE " +
//            "to_char( data_time, 'yyyy-MM-dd' ) = ?1 AND " +
            "indicator_first_level_code = ?5  group by mine_code ) maxTime \n" +
            "WHERE\n" +
            "\tsim.data_time = maxTime.maxTime \n" +
            "and sim.mine_code = maxTime.mine_code " +
            "\tAND sim.indicator_first_level_code = ?5 \n" +
            "\tAND (sim.indicator_third_level_code = '' or sim.indicator_third_level_code is null) group by sim.mine_code ",nativeQuery = true)
    List<Map<String, Object>> queryCompanyModelListV2(@Param("gasCode")String gasCode,@Param("fireCode")String fireCode,@Param("dustCode")String dustCode,@Param("waterCode")String waterCode,@Param("roofCode") String roofCode);


    @Query(value = "SELECT\n" +
            "\ti.indicator_first_level_name AS indicatorFirstLevelName,\n" +
            "\ti.indicator_second_level_code indicatorSecondLevelCode,\n" +
            "\tim.indicator_third_level_code indicatorThirdLevelCode,\n" +
            "\ti.indicator_third_level_name indicatorSecondLevelName,\n" +
            "\tim.indicator_value indicatorValue,\n" +
            "\tCOALESCE( round( ( round( 100 * i.indicator_weight_value, '3' )- im.indicator_value ), '3' ), '0' ) problemStr,\n" +
            "\tim.judge_reason \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model im\n" +
            "\tLEFT JOIN ssys_indicator i ON im.indicator_first_level_code = i.indicator_first_level_code \n" +
            "\tAND i.indicator_second_level_code = im.indicator_second_level_code \n" +
            "\tAND im.indicator_third_level_code = i.indicator_third_level_code \n" +
            "WHERE\n" +
            "\tim.data_time =(\n" +
            "\tSELECT\n" +
            "\t\tdata_time \n" +
            "\tFROM\n" +
            "\t\tssys_mine_indicator_model \n" +
            "\tWHERE\n" +
            "\t\tto_char(data_time,'yyyy-MM-dd') = ?1 \n" +
            "\t\t\tORDER BY data_time DESC LIMIT 1 ) \n" +
            "\t\t\tAND im.indicator_first_level_code = ?2\n" +
            "\t\t\n" +
            "\tAND im.indicator_third_level_code != ''",nativeQuery = true)
    List<Map<String,Object>> queryGasModelListV2(@Param("date") String date, @Param("code") String code);


    @Query(value = "SELECT\n" +
            "\ti.indicator_first_level_name AS indicatorFirstLevelName,\n" +
            "\ti.indicator_second_level_code indicatorSecondLevelCode,\n" +
            "\tim.indicator_third_level_code indicatorThirdLevelCode,\n" +
            "\ti.indicator_third_level_name indicatorSecondLevelName,\n" +
            "\tim.indicator_value indicatorValue,\n" +
            "\tCOALESCE( round( ( round( 100 * i.indicator_weight_value, '3' )- im.indicator_value ), '3' ), '0' ) problemStr,\n" +
            "\tim.judge_reason \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model im\n" +
            "\tLEFT JOIN ssys_indicator i ON im.indicator_first_level_code = i.indicator_first_level_code \n" +
            "\tAND i.indicator_second_level_code = im.indicator_second_level_code \n" +
            "\tAND im.indicator_third_level_code = i.indicator_third_level_code \n" +
            "WHERE\n" +
            "\tim.data_time = (\n" +
            "\tSELECT\n" +
            "\t\tdata_time \n" +
            "\tFROM\n" +
            "\t\tssys_mine_indicator_model \n" +
            "\tWHERE\n" +
            "\t\tto_char(data_time,'yyyy-MM-dd') = ?1 \n" +
            "\t\t\tORDER BY data_time DESC LIMIT 1 ) \n" +
            "\t\t\tAND im.indicator_first_level_code = ?2\n" +
            "\t\t\n" +
            "\tAND im.indicator_third_level_code != ''",nativeQuery = true)
    List<Map<String,Object>> queryFireModelListV2(@Param("date") String date, @Param("code") String code);

    @Query(value = "INSERT INTO \"znjc\".\"ssys_mine_indicator_model\" ( \"model_time\", \"data_time\", \"indicator_first_level_code\", \"indicator_second_level_code\", \"indicator_third_level_code\", \"indicator_value\", \"original_value\", \"judge_reason\" )\n" +
            "VALUES\n" +
            "\t( ?1, ?2, ?3, ?4, ?5,?6 , ?7, NULL ) RETURNING *",nativeQuery = true)
    Map<String,Object> saveModel(Date modelTime,
                                Date dateTime,
                                String indicatorFirstLevelCode,
                                String indicatorSecondLevelCode,
                                String indicatorThirdLevelCode,
                                String indicatorValue,
                                String originalValue);



    @Query(value = "SELECT\n" +
            "\tto_char(data_time, 'yyyy-MM-dd') dataStr,\n" +
            "\tSUM ( indicator_value ) totalVal \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model im \n" +
            "WHERE\n" +
            "\tim.indicator_first_level_code = ?1 \n" +
            "\tAND (indicator_third_level_code = '' or indicator_third_level_code is null)\n" +
            "\tAND to_char(data_time, 'yyyy-MM-dd') >= ?2 \n" +
            "AND to_char(data_time, 'yyyy-MM-dd')  <= ?3 \n" +
            "GROUP BY\n" +
            "\tim.data_time\n",nativeQuery = true)
    List<Map<String, Object>> queryGasModelListByDateV2(String code,String beginDate,String endDate);

    @Query(value = "SELECT\n" +
            "\tdata_time dataStr,\n" +
            "\tROUND( SUM ( indicator_value ), 4 ) totalVal \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model im \n" +
            "WHERE\n" +
            "\tim.indicator_first_level_code =?1 \n" +
            "\tAND im.data_time >=?2 \n" +
            "\tAND im.data_time <= ?3 \n" +
            "\tAND (im.indicator_third_level_code = '' or im.indicator_third_level_code is null) \n" +
            "GROUP BY\n" +
            "\tim.data_time",nativeQuery = true)
    List<Map<String, Object>> queryFireModelListByDateV2(String code,String beginDate,String endDate);
//    List<Map<String, Object>> queryFloodModelListByDate(String beginDate,String endDate);
//    List<Map<String, Object>> queryRoofModelListByDate(String beginDate,String endDate);
    @Query(value = "SELECT\n" +
            "\tdata_time dataStr,\n" +
            "\tROUND( SUM ( indicator_value ), 4 ) totalVal \n" +
            "FROM\n" +
            "\tssys_mine_indicator_model im \n" +
            "WHERE\n" +
            "\tim.indicator_first_level_code =?1 \n" +
            "\tAND im.data_time >=?2 \n" +
            "\tAND im.data_time <= ?3 \n" +
            "\tAND (im.indicator_third_level_code = '' or im.indicator_third_level_code is null) \n" +
            "GROUP BY\n" +
            "\tim.data_time",nativeQuery = true)
    List<Map<String, Object>> queryDustModelListByDateV2(String code,String beginDate,String endDate);

//    @Query(value = "",nativeQuery = true)
//    IPage<SysIndicatorModel> pageList(String name, Page pages);

    @Query(value = "SELECT\n" +
            "\t\"max\" ( cou.totalsum )  as  big,\n" +
            "\t\"min\"(cou.totalsum) as small,\n" +
            "\tto_char(\"avg\"(cou.totalsum),'90.99') as average\n" +
            "FROM\n" +
            "\t(\n" +
            "\tSELECT\n" +
            "\t\t'瓦斯' indicatorFirstLevelName,\n" +
            "\t\t'1' AS indicatorSecondLevelName,\n" +
            "\t\tSUM ( sim.indicator_value ) totalSum,\n" +
            "\t\tdata_time \n" +
            "\tFROM\n" +
            "\t\tssys_indicator_model sim \n" +
            "\tWHERE\n" +
            "\t\tto_char( data_time, 'yyyy-MM-dd' ) > '2023-01-01' \n" +
            "\t\tAND indicator_first_level_code = '20' \n" +
            "\t\tAND (indicator_third_level_code = '' or indicator_third_level_code is null) \n" +
            "\tGROUP BY\n" +
            "\tdata_time \n" +
            "\t) cou",nativeQuery = true)
    Map<String,Object> getScore(String dataTime,String type);
}
